﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using IDAL;
using System.Data.SqlClient;
using System.Configuration;
/***************************
 * 数据访问层
 * 系统用户数据crud
 * Author：JXL
 ***************************/ 
namespace DAL
{
    public class UsersDAL:BaseDAL,IUsersDAL 
        
    {
        //public static string Conns = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        protected override string tablename
        {
            get { return "UsersInfo"; }
        }

        protected override Dictionary<string, bool> pkname
        {
            get
            {
                var id = new Dictionary<string, bool>();
                id.Add("id", true);
                return id;
            }
        }

        /// <summary>
        /// 检查用户账户和密码是否存在
        /// </summary>
        /// <param name="mo"></param>
        /// <returns></returns>
        public Users UsersLogin(Users mo)
        {
            using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
            {
                conn.Open();
                string sql = string.Format("select * from Users where Users_Account='{0}' and Users_Pwd='{1}'", mo.users_Account, mo.users_Pwd);
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                Users Usemo = new Users();
                if (read.HasRows)
                {

                    while (read.Read())
                    {
                        Usemo.users_Id = Convert.ToInt32(read["users_Id"]);
                        Usemo.users_Name = read["Users_Name"].ToString();
                        //Usemo.users_Account = read["Users_Account"].ToString();
                        //Usemo.users_Pwd = read["Users_Pwd"].ToString();
                        //Usemo.users_TEL = read["Users_TEL"].ToString();
                        Usemo.Roles_id = Convert.ToInt32(read["Roles_id"]);
                        Usemo.Department_Id = Convert.ToInt32(read["Department_Id"]);
                        Usemo.users_IsState = Convert.ToInt32(read["Users_IsState"]);
                        //Usemo.users_CreateTimes = read["Users_CreateTime"].ToString();
                        //Usemo.users_CreatePeople = read["Users_CreatePeople"].ToString();

                    }

                }
                else
                {
                    return Usemo = null;
                }
                conn.Close();
                return Usemo;
            }
        }


        /// <summary>
        /// 显示用户人员
        /// </summary>
        /// <returns></returns>
        public List<Users> UsersShow()
        {
            using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
            {
                conn.Open();
                string sql = "select Users_Id,Users_Name,Users_Account,Users_TEL,CONVERT(varchar(50),Users_CreateTime,23) as Users_CreateTime,(case when Users_IsState=0 then '未启用' when Users_IsState=1 then '已启用'  end) as Users_IsState from Users";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<Users> list = new List<Users>();
                while (read.Read())
                {
                    Users mo = new Users();
                    mo.users_Id = Convert.ToInt32(read["Users_Id"]);
                    mo.users_Name = read["Users_Name"].ToString();
                    mo.users_Account = read["Users_Account"].ToString();
                    mo.users_TEL = read["Users_TEL"].ToString();
                    mo.users_CreateTimes = read["Users_CreateTime"].ToString();
                    mo.users_IsStates = read["Users_IsState"].ToString();
                    list.Add(mo);
                }
                conn.Close();
                return list;
            }
        }


        /// <summary>
        /// 将状态改为启用
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UsersFull(int users_Id)
        {
            using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
            {
                conn.Open();
                string sql = string.Format("update Users set Users_IsState=1 where Users_Id='{0}'", users_Id);
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }


        /// <summary>
        /// 将状态改为未启用
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UsersStop(int id)
        {
            using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
            {
                conn.Open();
                string sql = string.Format("update Users set Users_IsState=0 where Users_Id='{0}'", id);
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }


        /// <summary>
        /// 根据id来删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UsersDel(int id)
        {
            using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
            {
                conn.Open();
                string sql = string.Format("delete from Users where Users_Id='{0}'", id);
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }


        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="mo"></param>
        /// <returns></returns>
        public int UsereUpt(Users mo)
        {
            using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
            {
                conn.Open();
                string sql = string.Format("update Users set Users_Name='{0}',Users_TEL='{1}',Users_Pwd='{2}',Roles_id='{3}' where Users_Id='{4}'", mo.users_Name, mo.users_TEL, mo.users_Pwd, mo.Roles_id, mo.users_Id);
                SqlCommand cmd = new SqlCommand(sql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }



        /// <summary>
        /// 根据id来查询人员的详细讯息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Users> UsersSelectId(int id)
        {
            using (SqlConnection conn = new SqlConnection(HouseCommon.Common.conn))
            {
                conn.Open();
                string sql = string.Format("select Users_Id,Users_Name,Users_TEL,Users_Pwd,Roles_id from Users where Users_Id='{0}'", id);
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader read = cmd.ExecuteReader();
                List<Users> list = new List<Users>();
                while (read.Read())
                {
                    Users mo = new Users();
                    mo.users_Id = Convert.ToInt32(read["Users_Id"]);
                    mo.users_Name = read["Users_Name"].ToString();
                    mo.users_TEL = read["Users_TEL"].ToString();
                    mo.users_Pwd = read["Users_Pwd"].ToString();
                    mo.Roles_id = Convert.ToInt32(read["Roles_id"]);
                    list.Add(mo);
                }
                conn.Close();
                return list;
            }
        }
        /// <summary>
        /// 获取到各个员工的列表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Users> GetUsers(int id)
        {
            string str = "select * from Users where Roles_Id=" + id + "";
            return DBhelper.DBHelper.GetList<Users>(str);
        }
    }
}
